#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库表统计脚本

功能：
1. 统计项目中所有表的记录数
2. 获取每个表最新的一条记录的所有字段
3. 生成统计报告并保存为JSON文件

Author: Assistant
Date: 2025-01-27
"""

import os
import sys
import json
import mysql.connector
from datetime import datetime
from typing import Dict, Any, List

# 添加项目根目录到Python路径
sys.path.append('/Users/alan/Library/CloudStorage/SynologyDrive-Mac/财务解决方案/wealth_management_system/backend')

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'czya31505',
    'database': 'fund',
    'charset': 'utf8mb4'
}

def get_database_connection():
    """获取数据库连接"""
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        return connection
    except mysql.connector.Error as e:
        print(f"数据库连接失败: {e}")
        return None

def get_all_tables(connection) -> List[str]:
    """获取数据库中所有表名"""
    cursor = connection.cursor()
    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]
    cursor.close()
    return tables

def get_table_record_count(connection, table_name: str) -> int:
    """获取表的记录数"""
    cursor = connection.cursor()
    try:
        cursor.execute(f"SELECT COUNT(*) FROM `{table_name}`")
        count = cursor.fetchone()[0]
        return count
    except mysql.connector.Error as e:
        print(f"获取表 {table_name} 记录数失败: {e}")
        return 0
    finally:
        cursor.close()

def get_table_latest_record(connection, table_name: str) -> Dict[str, Any]:
    """获取表的最新一条记录"""
    cursor = connection.cursor(dictionary=True)
    try:
        # 先获取表结构，找到可能的时间字段
        cursor.execute(f"DESCRIBE `{table_name}`")
        columns = cursor.fetchall()
        
        # 查找时间相关字段
        time_columns = []
        for col in columns:
            col_name = col['Field'].lower()
            if any(keyword in col_name for keyword in ['created_at', 'updated_at', 'date', 'time']):
                time_columns.append(col['Field'])
        
        # 构建查询语句
        if time_columns:
            # 如果有时间字段，按时间字段排序
            order_by = f"ORDER BY `{time_columns[0]}` DESC"
        else:
            # 如果没有时间字段，尝试按主键排序
            cursor.execute(f"SHOW KEYS FROM `{table_name}` WHERE Key_name = 'PRIMARY'")
            primary_keys = cursor.fetchall()
            if primary_keys:
                order_by = f"ORDER BY `{primary_keys[0]['Column_name']}` DESC"
            else:
                order_by = ""
        
        query = f"SELECT * FROM `{table_name}` {order_by} LIMIT 1"
        cursor.execute(query)
        record = cursor.fetchone()
        
        if record:
            # 处理日期时间对象，转换为字符串
            for key, value in record.items():
                if isinstance(value, datetime):
                    record[key] = value.strftime('%Y-%m-%d %H:%M:%S')
                elif value is None:
                    record[key] = None
                else:
                    record[key] = str(value)
        
        return record or {}
    except mysql.connector.Error as e:
        print(f"获取表 {table_name} 最新记录失败: {e}")
        return {}
    finally:
        cursor.close()

def generate_statistics_report(connection) -> Dict[str, Any]:
    """生成统计报告"""
    print("正在生成数据库表统计报告...")
    
    tables = get_all_tables(connection)
    print(f"发现 {len(tables)} 个表")
    
    report = {
        'generated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'total_tables': len(tables),
        'tables_statistics': {}
    }
    
    total_records = 0
    
    for i, table in enumerate(tables, 1):
        print(f"处理表 {i}/{len(tables)}: {table}")
        
        record_count = get_table_record_count(connection, table)
        latest_record = get_table_latest_record(connection, table)
        
        report['tables_statistics'][table] = {
            'record_count': record_count,
            'latest_record': latest_record
        }
        
        total_records += record_count
        print(f"  - 记录数: {record_count}")
        if latest_record:
            print(f"  - 最新记录字段数: {len(latest_record)}")
    
    report['total_records'] = total_records
    print(f"\n统计完成！总记录数: {total_records}")
    
    return report

def save_report_to_file(report: Dict[str, Any], filename: str = None):
    """保存报告到JSON文件"""
    if filename is None:
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"tables_statistics_{timestamp}.json"
    
    filepath = os.path.join(os.path.dirname(__file__), filename)
    
    try:
        with open(filepath, 'w', encoding='utf-8') as f:
            json.dump(report, f, ensure_ascii=False, indent=2)
        print(f"报告已保存到: {filepath}")
    except Exception as e:
        print(f"保存报告失败: {e}")

def display_summary(report: Dict[str, Any]):
    """显示统计摘要"""
    print("\n" + "="*60)
    print("数据库表统计摘要")
    print("="*60)
    print(f"生成时间: {report['generated_at']}")
    print(f"总表数: {report['total_tables']}")
    print(f"总记录数: {report['total_records']}")
    print("\n表记录数排行榜（前10）:")
    
    # 按记录数排序
    sorted_tables = sorted(
        report['tables_statistics'].items(),
        key=lambda x: x[1]['record_count'],
        reverse=True
    )
    
    for i, (table_name, stats) in enumerate(sorted_tables[:10], 1):
        print(f"{i:2d}. {table_name:<30} {stats['record_count']:>10,} 条记录")
    
    print("\n空表列表:")
    empty_tables = [name for name, stats in sorted_tables if stats['record_count'] == 0]
    if empty_tables:
        for table in empty_tables:
            print(f"  - {table}")
    else:
        print("  无空表")

def main():
    """主函数"""
    print("数据库表统计脚本启动")
    print("连接数据库...")
    
    connection = get_database_connection()
    if not connection:
        print("无法连接到数据库，程序退出")
        return
    
    try:
        # 生成统计报告
        report = generate_statistics_report(connection)
        
        # 显示摘要
        display_summary(report)
        
        # 保存到文件
        save_report_to_file(report)
        
    except Exception as e:
        print(f"程序执行出错: {e}")
    finally:
        connection.close()
        print("\n数据库连接已关闭")

if __name__ == "__main__":
    main()